--揽收明细
with taking_deatil as(
select
   network_code
  ,max(network_name) as network_name
  ,count(1) as scan_sum
  ,'揽收量' as type1
  ,'揽收' as type2
  ,sum(is_00) as sum_hour_00
  ,sum(is_01) as sum_hour_01
  ,sum(is_02) as sum_hour_02
  ,sum(is_03) as sum_hour_03
  ,sum(is_04) as sum_hour_04
  ,sum(is_05) as sum_hour_05
  ,sum(is_06) as sum_hour_06
  ,sum(is_07) as sum_hour_07
  ,sum(is_08) as sum_hour_08
  ,sum(is_09) as sum_hour_09
  ,sum(is_10) as sum_hour_10
  ,sum(is_11) as sum_hour_11
  ,sum(is_12) as sum_hour_12
  ,sum(is_13) as sum_hour_13
  ,sum(is_14) as sum_hour_14
  ,sum(is_15) as sum_hour_15
  ,sum(is_16) as sum_hour_16
  ,sum(is_17) as sum_hour_17
  ,sum(is_18) as sum_hour_18
  ,sum(is_19) as sum_hour_19
  ,sum(is_20) as sum_hour_20
  ,sum(is_21) as sum_hour_21
  ,sum(is_22) as sum_hour_22
  ,sum(is_23) as sum_hour_23
     ,max(order_source_code)as order_source_code
      ,order_source_name
  ,dt
from (
         select tmp.waybill_no,
                network_code,
                network_name,
                if(hour(scan_time)=0,1,0) as is_00,
                if(hour(scan_time)=1,1,0) as is_01,
                if(hour(scan_time)=2,1,0) as is_02,
                if(hour(scan_time)=3,1,0) as is_03,
                if(hour(scan_time)=4,1,0) as is_04,
                if(hour(scan_time)=5,1,0) as is_05,
                if(hour(scan_time)=6,1,0) as is_06,
                if(hour(scan_time)=7,1,0) as is_07,
                if(hour(scan_time)=8,1,0) as is_08,
                if(hour(scan_time)=9,1,0) as is_09,
                if(hour(scan_time)=10,1,0) as is_10,
                if(hour(scan_time)=11,1,0) as is_11,
                if(hour(scan_time)=12,1,0) as is_12,
                if(hour(scan_time)=13,1,0) as is_13,
                if(hour(scan_time)=14,1,0) as is_14,
                if(hour(scan_time)=15,1,0) as is_15,
                if(hour(scan_time)=16,1,0) as is_16,
                if(hour(scan_time)=17,1,0) as is_17,
                if(hour(scan_time)=18,1,0) as is_18,
                if(hour(scan_time)=19,1,0) as is_19,
                if(hour(scan_time)=20,1,0) as is_20,
                if(hour(scan_time)=21,1,0) as is_21,
                if(hour(scan_time)=22,1,0) as is_22,
                if(hour(scan_time)=23,1,0) as is_23,
                to_date(scan_time) as dt,
                waybill_detail.ordersource_code as order_source_code,  --订单来源code
                waybill_detail.ordersource_name as order_source_name,  --订单来源name
                row_number() over (partition by tmp.waybill_no order by scan_time ) as rn
         from (
                  select waybill_no,
                         scan_site_code as network_code,
                         input_site     as network_name,
                         scan_time
                  from (
                        select
                             waybill_no,
                             scan_site_code,
                             max(input_site) as input_site,
                             min(scan_time)  as scan_time
                        from jms_dwd.dwd_tab_barscan_taking_base_dt a
                        where a.dt >= date_sub('{{ execution_date | cst_ds }}', 15)
                          and a.dt <= '{{ execution_date | cst_ds }}'
                          and to_date(a.scan_time) >= date_sub('{{ execution_date | cst_ds }}', 9)
                          and to_date(a.scan_time)<= '{{ execution_date | cst_ds }}'
                        group by waybill_no, scan_site_code
                       )taking_base
                  union all
                  select
                         waybill_no,
                         scan_site_code as  network_code,
                         input_site     as network_name,
                         scan_time
                  from (
                         select
                                waybill_no,
                                scan_site_code,
                                max(input_site) as input_site,
                                min(scan_time)  as scan_time
                       from jms_dwd.dwd_tab_barscan_warehousing_base_dt a
                       where a.dt >= date_sub('{{ execution_date | cst_ds }}', 15)
                         and a.dt <= '{{ execution_date | cst_ds }}'
                         and to_date(a.scan_time) >= date_sub('{{ execution_date | cst_ds }}', 9)
                         and to_date(a.scan_time)<= '{{ execution_date | cst_ds }}'
                       group by waybill_no, scan_site_code
                       ) warehousing_base
              ) tmp
         left join (
             select waybill_no,ordersource_code,ordersource_name
             from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
                 where dt >= date_sub('{{ execution_date | cst_ds }}', 45)
                 and dt <= '{{ execution_date | cst_ds }}'
             ) waybill_detail
    on tmp.waybill_no=waybill_detail.waybill_no
     )temp2
where temp2.rn=1
group by network_code,dt,order_source_name
),
--时效签收明细
aging_sign_detail as(
select
  network_code,
  max(network_name) as network_name,
  count(1) as scan_sum,
  '时效签收量' as type1,
  aging_sign_scan_type as type2,
  sum(is_00) as sum_hour_00,
  sum(is_01) as sum_hour_01,
  sum(is_02) as sum_hour_02,
  sum(is_03) as sum_hour_03,
  sum(is_04) as sum_hour_04,
  sum(is_05) as sum_hour_05,
  sum(is_06) as sum_hour_06,
  sum(is_07) as sum_hour_07,
  sum(is_08) as sum_hour_08,
  sum(is_09) as sum_hour_09,
  sum(is_10) as sum_hour_10,
  sum(is_11) as sum_hour_11,
  sum(is_12) as sum_hour_12,
  sum(is_13) as sum_hour_13,
  sum(is_14) as sum_hour_14,
  sum(is_15) as sum_hour_15,
  sum(is_16) as sum_hour_16,
  sum(is_17) as sum_hour_17,
  sum(is_18) as sum_hour_18,
  sum(is_19) as sum_hour_19,
  sum(is_20) as sum_hour_20,
  sum(is_21) as sum_hour_21,
  sum(is_22) as sum_hour_22,
  sum(is_23) as sum_hour_23,
  order_source_code,  --订单来源code
  max(order_source_name) as order_source_name,  --订单来源
       dt
from (
         select waybill_no,
                network_code,
                network_name,
                aging_sign_scan_type,
                if(hour(scan_time) = 0, 1, 0)  as is_00,
                if(hour(scan_time) = 1, 1, 0)  as is_01,
                if(hour(scan_time) = 2, 1, 0)  as is_02,
                if(hour(scan_time) = 3, 1, 0)  as is_03,
                if(hour(scan_time) = 4, 1, 0)  as is_04,
                if(hour(scan_time) = 5, 1, 0)  as is_05,
                if(hour(scan_time) = 6, 1, 0)  as is_06,
                if(hour(scan_time) = 7, 1, 0)  as is_07,
                if(hour(scan_time) = 8, 1, 0)  as is_08,
                if(hour(scan_time) = 9, 1, 0)  as is_09,
                if(hour(scan_time) = 10, 1, 0) as is_10,
                if(hour(scan_time) = 11, 1, 0) as is_11,
                if(hour(scan_time) = 12, 1, 0) as is_12,
                if(hour(scan_time) = 13, 1, 0) as is_13,
                if(hour(scan_time) = 14, 1, 0) as is_14,
                if(hour(scan_time) = 15, 1, 0) as is_15,
                if(hour(scan_time) = 16, 1, 0) as is_16,
                if(hour(scan_time) = 17, 1, 0) as is_17,
                if(hour(scan_time) = 18, 1, 0) as is_18,
                if(hour(scan_time) = 19, 1, 0) as is_19,
                if(hour(scan_time) = 20, 1, 0) as is_20,
                if(hour(scan_time) = 21, 1, 0) as is_21,
                if(hour(scan_time) = 22, 1, 0) as is_22,
                if(hour(scan_time) = 23, 1, 0) as is_23,
                order_source_code,  --订单来源
                order_source_name,  -- 订单来源
                dt
         from (
                  select billcode                 as waybill_no,
                         final_sign_network_code  as network_code,
                         final_sign_network_name  as network_name,
                         aging_sign_time          as scan_time,
                         if(aging_sign_scan_type= '邮政','转邮件',aging_sign_scan_type ) as aging_sign_scan_type,
                      order_source_code,  --订单来源
                      order_source_name,  -- 订单来源
                         to_date(aging_sign_time) as dt
                  from jms_dm.dm_terminal_sign_detail_new_dt
                  where dt >= date_sub('{{ execution_date | cst_ds }}', 15)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and to_date(aging_sign_time) >= date_sub('{{ execution_date | cst_ds }}', 9)
                    and to_date(aging_sign_time) <= '{{ execution_date | cst_ds }}'
              ) tmp
     )tmp2
group by network_code,dt,aging_sign_scan_type,order_source_code
),
--实际签收明细
active_sign_detail as(
-- 时效签收
select
  network_code,
  max(network_name) as network_name,
  count(1) as scan_sum,
  '实际签收量' as type1,
  actual_sign_scan_type as type2,
  sum(is_00) as sum_hour_00,
  sum(is_01) as sum_hour_01,
  sum(is_02) as sum_hour_02,
  sum(is_03) as sum_hour_03,
  sum(is_04) as sum_hour_04,
  sum(is_05) as sum_hour_05,
  sum(is_06) as sum_hour_06,
  sum(is_07) as sum_hour_07,
  sum(is_08) as sum_hour_08,
  sum(is_09) as sum_hour_09,
  sum(is_10) as sum_hour_10,
  sum(is_11) as sum_hour_11,
  sum(is_12) as sum_hour_12,
  sum(is_13) as sum_hour_13,
  sum(is_14) as sum_hour_14,
  sum(is_15) as sum_hour_15,
  sum(is_16) as sum_hour_16,
  sum(is_17) as sum_hour_17,
  sum(is_18) as sum_hour_18,
  sum(is_19) as sum_hour_19,
  sum(is_20) as sum_hour_20,
  sum(is_21) as sum_hour_21,
  sum(is_22) as sum_hour_22,
  sum(is_23) as sum_hour_23,
  max(order_source_code) as order_source_code,
  order_source_name,
  dt
from (
         select waybill_no,
                network_code,
                network_name,
                actual_sign_scan_type,
                if(hour(scan_time) = 0, 1, 0)  as is_00,
                if(hour(scan_time) = 1, 1, 0)  as is_01,
                if(hour(scan_time) = 2, 1, 0)  as is_02,
                if(hour(scan_time) = 3, 1, 0)  as is_03,
                if(hour(scan_time) = 4, 1, 0)  as is_04,
                if(hour(scan_time) = 5, 1, 0)  as is_05,
                if(hour(scan_time) = 6, 1, 0)  as is_06,
                if(hour(scan_time) = 7, 1, 0)  as is_07,
                if(hour(scan_time) = 8, 1, 0)  as is_08,
                if(hour(scan_time) = 9, 1, 0)  as is_09,
                if(hour(scan_time) = 10, 1, 0) as is_10,
                if(hour(scan_time) = 11, 1, 0) as is_11,
                if(hour(scan_time) = 12, 1, 0) as is_12,
                if(hour(scan_time) = 13, 1, 0) as is_13,
                if(hour(scan_time) = 14, 1, 0) as is_14,
                if(hour(scan_time) = 15, 1, 0) as is_15,
                if(hour(scan_time) = 16, 1, 0) as is_16,
                if(hour(scan_time) = 17, 1, 0) as is_17,
                if(hour(scan_time) = 18, 1, 0) as is_18,
                if(hour(scan_time) = 19, 1, 0) as is_19,
                if(hour(scan_time) = 20, 1, 0) as is_20,
                if(hour(scan_time) = 21, 1, 0) as is_21,
                if(hour(scan_time) = 22, 1, 0) as is_22,
                if(hour(scan_time) = 23, 1, 0) as is_23,
                 order_source_code,  --订单来源
                  order_source_name,  -- 订单来源
                dt
         from (
               select
                   billcode as waybill_no,
                   final_sign_network_code  as network_code,
                   final_sign_network_name  as network_name,
                   actual_sign_time          as scan_time,
                   actual_sign_scan_type,
                      order_source_code,  --订单来源
                      order_source_name,  -- 订单来源
                   to_date(actual_sign_time) as dt
               from jms_dm.dm_terminal_sign_detail_new_dt
              where dt between date_format(date_add('{{ execution_date | cst_ds }}', -15), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
               and to_date(actual_sign_time) >= date_sub('{{ execution_date | cst_ds }}', 9)
               and to_date(actual_sign_time) <= '{{ execution_date | cst_ds }}'
              ) tmp
     )tmp2
group by network_code,dt,actual_sign_scan_type,order_source_name
),

--出仓明细
delver_detail as(
select
  network_code,
  max(network_name) as network_name,
  count(1) as scan_sum,
  '派件量' as type1,
  null as type2,
  sum(is_00) as sum_hour_00,
  sum(is_01) as sum_hour_01,
  sum(is_02) as sum_hour_02,
  sum(is_03) as sum_hour_03,
  sum(is_04) as sum_hour_04,
  sum(is_05) as sum_hour_05,
  sum(is_06) as sum_hour_06,
  sum(is_07) as sum_hour_07,
  sum(is_08) as sum_hour_08,
  sum(is_09) as sum_hour_09,
  sum(is_10) as sum_hour_10,
  sum(is_11) as sum_hour_11,
  sum(is_12) as sum_hour_12,
  sum(is_13) as sum_hour_13,
  sum(is_14) as sum_hour_14,
  sum(is_15) as sum_hour_15,
  sum(is_16) as sum_hour_16,
  sum(is_17) as sum_hour_17,
  sum(is_18) as sum_hour_18,
  sum(is_19) as sum_hour_19,
  sum(is_20) as sum_hour_20,
  sum(is_21) as sum_hour_21,
  sum(is_22) as sum_hour_22,
  sum(is_23) as sum_hour_23,
  max(order_source_code) as order_source_code,
  order_source_name,
  dt
from (
         select tmp.waybill_no,
                network_code,
                network_name,
                if(hour(scan_time) = 0, 1, 0)  as is_00,
                if(hour(scan_time) = 1, 1, 0)  as is_01,
                if(hour(scan_time) = 2, 1, 0)  as is_02,
                if(hour(scan_time) = 3, 1, 0)  as is_03,
                if(hour(scan_time) = 4, 1, 0)  as is_04,
                if(hour(scan_time) = 5, 1, 0)  as is_05,
                if(hour(scan_time) = 6, 1, 0)  as is_06,
                if(hour(scan_time) = 7, 1, 0)  as is_07,
                if(hour(scan_time) = 8, 1, 0)  as is_08,
                if(hour(scan_time) = 9, 1, 0)  as is_09,
                if(hour(scan_time) = 10, 1, 0) as is_10,
                if(hour(scan_time) = 11, 1, 0) as is_11,
                if(hour(scan_time) = 12, 1, 0) as is_12,
                if(hour(scan_time) = 13, 1, 0) as is_13,
                if(hour(scan_time) = 14, 1, 0) as is_14,
                if(hour(scan_time) = 15, 1, 0) as is_15,
                if(hour(scan_time) = 16, 1, 0) as is_16,
                if(hour(scan_time) = 17, 1, 0) as is_17,
                if(hour(scan_time) = 18, 1, 0) as is_18,
                if(hour(scan_time) = 19, 1, 0) as is_19,
                if(hour(scan_time) = 20, 1, 0) as is_20,
                if(hour(scan_time) = 21, 1, 0) as is_21,
                if(hour(scan_time) = 22, 1, 0) as is_22,
                if(hour(scan_time) = 23, 1, 0) as is_23,
                 waybill_detail.ordersource_code as order_source_code,  --订单来源
                 waybill_detail.ordersource_name as order_source_name,  -- 订单来源
                dt
         from (
                                select waybill_no,
                                       scan_site_code as network_code,
                                       scan_site as network_name,
                                      scan_time,
                                       row_number() over (partition by to_date(scan_time),waybill_no order by scan_time )as rn,
                                       to_date(scan_time)   as dt
                                from jms_dwd.dwd_tab_barscan_deliver_base_dt
                            where dt>=date_sub('{{ execution_date | cst_ds }}',20)
                           and dt<='{{ execution_date | cst_ds }}'
                           and to_date(scan_time)>date_sub('{{ execution_date | cst_ds }}',10)
                           and to_date(scan_time)<='{{ execution_date | cst_ds }}'
                           and scan_site_code is not null
                            ) tmp
                  left join (
                       select waybill_no,ordersource_code,ordersource_name
             from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
                 where dt >= date_sub('{{ execution_date | cst_ds }}', 45)
                 and dt <= '{{ execution_date | cst_ds }}'
             ) waybill_detail
                  on tmp.waybill_no=waybill_detail.waybill_no
                  where tmp.rn=1
              ) tmp

group by network_code,dt,order_source_name
)
insert overwrite table jms_dm.dm_network_taking_union_sign_hour_sum_dt partition(dt)
--揽签分布汇总表
select
   b.manage_code  --管理大区
  ,b.manage_name  --管理大区名字
  ,b.agent_code   --代理区
  ,b.agent_name   --代理区名字
  ,b.fran_code    --加盟商
  ,b.fran_name    --加盟商名字
  ,network_code   --网点code
  ,network_name   --网点名字
  ,scan_sum       --当时汇总
  ,type1          -- 类型一 包含揽收、时效签收、实际签收
  ,type2          --类型2
  ,sum_hour_00    --0点到1点量
  ,sum_hour_01    --1点到2点量
  ,sum_hour_02    --2点到3点量
  ,sum_hour_03    --3点到4点量
  ,sum_hour_04    --4点到5点量
  ,sum_hour_05    --5点到6点量
  ,sum_hour_06    --6点到7点量
  ,sum_hour_07    --7点到8点量
  ,sum_hour_08    --8点到9点量
  ,sum_hour_09    --9点到10点量
  ,sum_hour_10    --10点到11点量
  ,sum_hour_11    --11点到12点量
  ,sum_hour_12    --12点到13点量
  ,sum_hour_13    --13点到14点量
  ,sum_hour_14    --14点到15点量
  ,sum_hour_15    --15点到16点量
  ,sum_hour_16    --16点到17点量
  ,sum_hour_17    --17点到18点量
  ,sum_hour_18    --18点到19点量
  ,sum_hour_19    --19点到20点量
  ,sum_hour_20    --20点到21点量
  ,sum_hour_21    --21点到22点量
  ,sum_hour_22    --22点到23点量
  ,sum_hour_23    --23点到24点量
  ,dt as date_time --业务日期
  ,order_source_code --订单来源code
  ,order_source_name --订单来源
  ,dt   --日期
from (
select *
from taking_deatil
union all
select *
from aging_sign_detail
union all
select *
from active_sign_detail
union all
select *
from delver_detail
    )tmp
join (select *
from jms_dim.dim_network_whole_massage
    where network_type=6
    ) b
on tmp.network_code=b.code
distribute by 20
;
